用户行为数据分析

2023-06-12 15:40| 来源: 网络整理| 查看: 265

文章目录用户行为数据分析1 项目描述2 项目需求3 数据准备1、创建user_data数据表用于导入user_data.csv中的数据2、加载user_data.csv中的数据到user_data表3、接下来进行数据清洗，包括：删除重复值，时间戳格式化，删除异常值。 4 统计分析1、查询总访问量PV，总用户量UV2、查询日均访问量，日均用户量3、查询每个用户的购物情况4、根据user_behavior_count的结果查询复购率5、统计转化率6、统计一天内活跃时段点击数、收藏数、加购物车数、购买数的分布7、统计周用户点击数、收藏数、加购物车数、购买数的活跃分布8、统计用户最近一次的购买时间，降序排序只显示前10条记录9、统计消费频率最高的前10名用户

用户行为数据分析 1 项目描述

user_data.csv是一份用户行为数据，时间区间为2017-11-25到2017-12-03，总计29132493条记录，大小为1.0G，包含5个字段。数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下：

列名称字段名数据类型说明用户IDuser_idstring用户ID商品IDitem_idstring商品ID商品类目IDcategory_idstring商品类目ID行为类型behavior_typestring行为类型，包括(pv, buy, cart, fav)时间戳create_timeint行为时间戳

用户行为类型共有四种，它们分别是

行为类型说明pv商品详情页pv，等价于点击buy商品购买cart将商品加入购物车fav收藏商品 2 项目需求

1、查询总访问量PV，总用户量UV

2、查询日均访问量，日均用户量

3、查询每个用户的购物情况（统计点击、商品购买、加入购物车、收藏的次数），统计结果放入表user_behavior_count

4、根据user_behavior_count的结果查询复购率：产生两次或两次以上购买的用户占购买用户的比例

5、统计用户各环节行为转化率，分别统计从“点击”到“(加购物车+收藏)”的转化率，和从“(加购物车+收藏)”到“购买”的转化率

6、统计一天内活跃时段点击数、收藏数、加购物车数、购买数的分布

7、统计周用户击数、收藏数、加购物车数、购买数的活跃分布

8、统计用户最近一次的购买时间，降序排序只显示前10个记录

9、统计消费频率最高的前10名用户

3 数据准备

将数据加载到hive，然后通过hive对数据进行处理

1、创建user_data数据表用于导入user_data.csv中的数据 -- 创建表 create table if not exists user_data ( user_id string COMMENT '用户ID', item_id string COMMENT '商品ID', category_id string COMMENT '商品类目ID', behavior_type string COMMENT '行为类型', create_time int COMMENT '行为时间戳' ) row format delimited fields terminated by ',' stored as textfile;

2、加载user_data.csv中的数据到user_data表

上传user_data.csv到虚拟机：

加载数据到user_data表：

load data local inpath '/root/data/user_data.csv' into table user_data;

测试数据是否导入：

select * from user_data limit 10;

结果为：

3、接下来进行数据清洗，包括：删除重复值，时间戳格式化，删除异常值。

（1）user_data表中的create_time是int类型，是时间戳字段。在实际使用时时间戳字段使用不方便，为了使用方便需要把时间戳字段改为日期时间字符串。创建user_data_new表，把其中的create_time字段数据类型从int改为string类型，用于存储日期时间字符串。

-- 创建表 create table if not exists user_data_new ( user_id string COMMENT '用户ID', item_id string COMMENT '商品ID', category_id string COMMENT '商品类目ID', behavior_type string COMMENT '行为类型', create_time string COMMENT '行为时间戳' ) row format delimited fields terminated by ',' stored as textfile;

结果为：

（2）对user_data进行数据清洗，去掉完全重复的数据。分组操作可以达到去重的目的，对谁去重就对谁分组。

查看去重前数据量：

select count(1) from user_data;

结果为：

数据去重：

insert overwrite table user_data select user_id, item_id, category_id, behavior_type, create_time from user_data group by user_id, item_id, category_id, behavior_type, create_time;

查看去重后数据量：

select count(1) from user_data;

结果为：

（3）对user_data进行数据清洗，int类型的时间戳格式化成日期时间字符串，新数据存于user_data_new表中。使用函数from_unixtime进行日期时间格式化。

insert overwrite table user_data_new select user_id, item_id, category_id, behavior_type, from_unixtime(create_time, 'yyyy-MM-dd HH:mm:ss') from user_data;

查看数据是否格式化成功：

select * from user_data_new limit 10;

（4）查看user_data_new表中create_time字段是否有异常值，为NULL的或不在统计时间范围内的

方法一：直接查找

select * from user_data_new where date(create_time) not between '2017-11-25' and '2017-12-03' or create_time is null;

发现有异常值：

我们发现异常数据也是比较大的，不太容易观察。

方法二：根据group分组聚合

select date(create_time) as day from user_data_new group by date(create_time) having day not between '2017-11-25' and '2017-12-03' or day is null order by day;

结果为：

发现异常值还是比较多的。

（5）对user_data_new表进行数据清洗，去掉时间异常的数据。把create_time处于’2017-11-25’ 和 '2017-12-03’之间的数据认为是正常数据。

cast(create_time as date)可以把日期时间字符串转为日期

insert overwrite table user_data_new select user_id, item_id, category_id, behavior_type, create_time from user_data_new where cast(create_time as date) between '2017-11-25' and '2017-12-03';

检查一下是否还有异常数据：

create_time 没有异常数据了。

（6）查看 user_data_new表中behavior_type 是否有异常值，可用分组运算查看behavior_type的值

select behavior_type from user_data_new group by behavior_type;

behavior_type 发现没有异常值。

4 统计分析 1、查询总访问量PV，总用户量UV select sum(if(behavior_type = 'pv', 1, 0)) as pv, count(distinct user_id) as uv from user_data_new;

结果为：

2、查询日均访问量，日均用户量

日期比较函数： datediff

语法： datediff(string enddate, string startdate)

返回值： int

说明：返回结束日期减去开始日期的天数。

-- datediff(string enddate, string startdate)得到的天数需要加1 select sum(if(behavior_type = 'pv', 1, 0)) / (datediff('2017-12-03', '2017-11-25') + 1) as avg_pv, count(distinct user_id) / (datediff('2017-12-03', '2017-11-25') + 1) as avg_uv from user_data_new;

结果为：

每日访问量，每日用户量（建议使用这个）：

select date(create_time) as day, sum(if(behavior_type = 'pv', 1, 0)) as pv, count(distinct user_id) as uv from user_data_new group by date(create_time) order by day;

结果为;

3、查询每个用户的购物情况

查询每个用户的购物情况（统计点击、商品购买、加入购物车、收藏的次数），统计结果放入表user_behavior_count。

create table user_behavior_count as select user_id, sum(if(behavior_type = 'pv', 1, 0)) pv, sum(if(behavior_type = 'buy', 1, 0)) buy, sum(if(behavior_type = 'cart', 1, 0)) cart, sum(if(behavior_type = 'fav', 1, 0)) fav from user_data_new group by user_id;

查看数据是否导入表中：

select * from user_behavior_count limit 10;

结果为：

4、根据user_behavior_count的结果查询复购率

复购率：产生两次或两次以上购买的用户占购买用户的比例

select sum(if(buy > 1, 1, 0)) / sum(if(buy > 0, 1, 0)) rate from user_behavior_count;

结果为：

5、统计转化率

统计用户各环节行为转化率，分别统计从“点击”到“(加购物车+收藏)”的转化率，和从“(加购物车+收藏)”到“购买”的转化率。

select round(sum(cart + fav) / sum(pv), 4), round(sum(buy) / sum(cart + fav), 4) from user_behavior_count;

结果为：

从“点击”到“(加购物车+收藏)”的转化率为0.0946，比较低，可能由于多种因素导致，以下是一些常见的原因：

网站设计不够吸引人：用户在点击进入网站后，如果网站布局、颜色、文字排版等没有吸引力，用户可能会失去兴趣，不再进行后续的操作；

目标用户不明确：如果网站的推广广告或者流量来源渠道不够明确，会吸引一些不符合目标用户要求的人群，他们不会对网站感兴趣，因此不会进行加购物车或收藏操作；

商品的价格过高或者质量不好：如果网站商品的价格过高或者质量不好，用户可能会选择不进行购买、加购物车或者收藏的操作；

用户体验不佳：如果进行加购物车或者收藏的流程复杂、页面加载时间较长等，会影响用户体验，导致用户放弃操作；

流量质量差：如果网站的流量质量较差，也会导致用户没有进行加购物车或收藏的行为。

从“(加购物车+收藏)”到“购买”的转化率为0.2377，相对较高，可能是因为进行加购物车和收藏的都是对商品感兴趣的，他们购买商品的意愿更强烈。

6、统计一天内活跃时段点击数、收藏数、加购物车数、购买数的分布

思路：我是按照天、小时进行分组然后聚合的。

select date(create_time) day, hour(create_time) hour, sum(if(behavior_type = 'pv', 1, 0)) pv, sum(if(behavior_type = 'buy', 1, 0)) buy, sum(if(behavior_type = 'cart', 1, 0)) cart, sum(if(behavior_type = 'fav', 1, 0)) fav from user_data_new group by date(create_time), hour(create_time) -- 不能使用别名 order by day, hour limit 10;

结果为：

也可以使用字符串函数截取，然后分组聚合。

有人是按照时间进行划分的：

select hour(create_time) hour, sum(if(behavior_type = 'pv', 1, 0)) pv, sum(if(behavior_type = 'buy', 1, 0)) buy, sum(if(behavior_type = 'cart', 1, 0)) cart, sum(if(behavior_type = 'fav', 1, 0)) fav from user_data_new group by hour(create_time) order by hour;

结果为：在这里插入图片描述

7、统计周用户点击数、收藏数、加购物车数、购买数的活跃分布

如何计算周几？

方法一：这样得到的周日为第0天。

pmod(int a, int b)，pmod(double a, double b)：返回a除b的余数的绝对值。

datediff(endDate, startDate)：返回startDate到endDate相差的天数

计算create_time所代表的日期是星期几，pmod(datediff(create_time, ‘1920-01-01’) - 3, 7)

方法二：这样得到的周日为第7天。

select dayofweek('2017-11-25');

以第一种方法为例：

select month(create_time) month, pmod(datediff(create_time, '1920-01-01') - 3, 7) weekday, sum(if(behavior_type = 'pv', 1, 0)) pv, sum(if(behavior_type = 'fav', 1, 0)) fav, sum(if(behavior_type = 'cart', 1, 0)) cart, sum(if(behavior_type = 'buy', 1, 0)) buy from user_data_new group by month(create_time), pmod(datediff(create_time, '1920-01-01') - 3, 7) order by month, weekday;

结果为：

8、统计用户最近一次的购买时间，降序排序只显示前10条记录 select user_id, max(create_time) latest_date from user_data_new where behavior_type = 'buy' --指定行为为buy group by user_id order by latest_date desc limit 10;

结果为：

9、统计消费频率最高的前10名用户 select user_id, buy from user_behavior_count order by buy desc limit 10;

结果为：

参考文章：

https://blog.csdn.net/weixin_46436010/article/details/129732809?spm=1001.2014.3001.5502

【本文地址】

公司简介

联系我们